"""
-- coding: utf-8 --
@Time : 2023/8/1
@Author : 张书译
@Describe: Excel表格操作
"""

from datetime import datetime
import xlrd
from xlrd import xldate_as_tuple
from xlutils.copy import copy
from Logger import logger


class ExcelMethod:
    """
    excel方法
    """

    def __init__(self, path, sheet=None):
        """
        初始化函数
        """
        self.path = path
        self.workBook = xlrd.open_workbook(path)
        """获取所有的sheet名字"""
        self.allSheetNames = self.workBook.sheet_names()
        if sheet is None:
            sheet = self.allSheetNames[0]
        """按sheet名字获取sheet内容"""
        self.sheet_content = self.workBook.sheet_by_name(sheet)
        """按sheet名称锁定"""
        self.w = copy(self.workBook)
        self.Gsheet = self.w.get_sheet(sheet)
        self.name = self.sheet_content.name  # 名称
        self.row = self.sheet_content.nrows  # 行数
        self.col = self.sheet_content.ncols  # 列数

    def read_row(self, rows):
        """读取全部一行的数据"""
        row_value = self.sheet_content.row_values(rows)
        return row_value

    def read_col(self, cols):
        """读取全部一列的数据"""
        col_value = self.sheet_content.cell_value(cols)
        return col_value

    def write_XY(self, row, cols, value):
        """写入Excel表格数据"""
        self.Gsheet.write(row, cols, value)
        logger.info(f"行：{row} 列：{cols} 值：{value}")
        self.w.save(self.path)

    def return_col(self, cols):
        """
        读取时间
        :param cols:
        :return:
        """
        col_value = []
        for i in range(1, self.row):
            xy_value = self.read_xy(i, cols)
            if self.sheet_content.cell(i, cols).ctype == 3:
                try:
                    xy_value = xldate_as_tuple(xy_value, 0)
                    xy_value = datetime(*xy_value)
                    xy_value = f"{xy_value}"
                except Exception as e:
                    logger.error(f"错误在这里：{e}")
                    xy_value = None
            elif ".0" in f"{xy_value}":
                xy_value = int(xy_value)
            elif xy_value == "":
                xy_value = None
            col_value.append(xy_value)
        return col_value

    def return_row(self, row):
        """
        读取时间
        :param row:
        :return:
        """
        row_value = []
        for i in range(1, self.col):
            xy_value = self.read_xy(row, i)
            if self.sheet_content.cell(row, i).ctype == 3:
                try:
                    xy_value = xldate_as_tuple(xy_value, 0)
                    xy_value = datetime(*xy_value)
                    xy_value = f"{xy_value}"
                except Exception as e:
                    logger.error(f"错误在这里：{e}")
                    xy_value = None
            elif ".0" in f"{xy_value}":
                xy_value = int(xy_value)
            elif xy_value == "":
                xy_value = None
            row_value.append(xy_value)
        return row_value

    def read_xy(self, row, cols):
        """读取指定的表格名"""
        xy_value = self.sheet_content.cell(row, cols).value
        if self.sheet_content.cell(row, cols).ctype == 3:
            try:
                xy_value = xldate_as_tuple(xy_value, 0)
                xy_value = datetime(*xy_value)
                xy_value = f'{xy_value}'
            except Exception as e:
                logger.error(f"错误在这里：{e}")
                xy_value = None
        return xy_value

    def rows(self):
        """返回所有行数"""
        return self.row

    def cols(self):
        """返回所有列数"""
        return self.col

    def names(self):
        """返回excel所有sheet名称"""
        return self.name
